package dao.mysql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.OvertimeDTO;
import dao.DAOException;
import dao.OvertimeDAO;

public class OvertimeDAOImpl implements OvertimeDAO  {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	// Create OneEmployee instance	

	public OvertimeDTO createOneOvertime() {
        return new OvertimeDTO();
  }
	
	@Override
	public OvertimeDTO findOvertime(int employeeID) throws DAOException {
		// TODO Auto-generated method stub
		String selectSql = "SELECT * FROM overtime WHERE EmployeeID='" + employeeID
		+ "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		OvertimeDTO overtime = new OvertimeDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				overtime.setEmployeeID(rs.getInt("EmployeeID"));
				overtime.setDate(rs.getDate("Date"));
				overtime.setTotalHours(rs.getDouble("TotalHours"));
				overtime.setStatus(rs.getString("Status"));
				overtime.setCreateBy(rs.getInt("CreateBy"));
				overtime.setCreateDate(rs.getDate("CreateDate"));
				overtime.setModifiedBy(rs.getInt("ModifiedBy"));
				overtime.setModifiedDate(rs.getDate("ModifiedDate"));
				overtime.setDescription(rs.getString("Description"));	
				}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(overtime.toString());
		return overtime;
	}
	
	@Override
	public void addOvertime(OvertimeDTO oneOvertime) throws SQLException, DAOException {
		// TODO Auto-generated method stub
		System.out.println("DAOresult====="+oneOvertime.toString());
		String insertSql = "INSERT INTO overtime (EmployeeID,Date,TotalHours,Status, CreateBy,CreateDate,ModifiedBy,ModifiedDate,Description) VALUES(" + oneOvertime.getEmployeeID()
		+ ", '" + oneOvertime.getDate() + "', '" + oneOvertime.getTotalHours()
		+ "', '" + oneOvertime.getStatus()+ "', '" + oneOvertime.getCreateBy()
		+ "', '" + oneOvertime.getCreateDate()+ "', '" + oneOvertime.getModifiedBy()
		+ "', '" + oneOvertime.getModifiedDate()+ "', '" + oneOvertime.getDescription()
		+ "');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	/*private int getNewOvertimeID() throws SQLException{
		int id = countAllOvertime();
		id ++;
		return id;
	}
*/
	public int countAllOvertime() throws SQLException {

	    String sql = "SELECT count(*) FROM overtime";
	    PreparedStatement stmt = null;
	    ResultSet result = null;
	    int allRows = 0;

	    try {
	    	Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
	        stmt = conn.prepareStatement(sql);
	        result = stmt.executeQuery();

	        if (result.next())
	            allRows = result.getInt(1);
	    } finally {
	        if (result != null)
	            result.close();
	        if (stmt != null)
	            stmt.close();
	    }
	    return allRows;
	}

	@Override
	public void updateOvertime(OvertimeDTO oneOvertime) throws DAOException,
			SQLException {
		// TODO Auto-generated method stub
		String updateSql = "UPDATE overtime SET EmployeeID="+ oneOvertime.getEmployeeID()
		+ "', Date='" + oneOvertime.getDate() + "', TotalHours='" + oneOvertime.getTotalHours()
		+ "', Status='" + oneOvertime.getStatus()+ "', CreateBy='" + oneOvertime.getCreateBy()
		+ "', CreateDate='" + oneOvertime.getCreateDate()+ "',ModifiedBy= '" + oneOvertime.getModifiedBy()
		+ "', ModifiedDate='" + oneOvertime.getModifiedDate()+ "', Description='" + oneOvertime.getDescription()
		+ "'WHERE OwerTimeID =" + oneOvertime.getOvertimeID()+ ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	@Override
	public void deleteOvertime(OvertimeDTO oneOvertime) throws DAOException,
			SQLException {
		// TODO Auto-generated method stub
		String deleteSql = "DELETE FROM overtime WHERE OverTimeID='"
			+ oneOvertime.getOvertimeID() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			int rows = st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}	
	
	@Override
	public ArrayList<OvertimeDTO> findAllOvertime() throws DAOException {
		// TODO Auto-generated method stub
		ArrayList<OvertimeDTO> items = new ArrayList<OvertimeDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM overtime;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				OvertimeDTO overtime = new OvertimeDTO();
				overtime.setEmployeeID(rs.getInt("EmployeeID"));
				overtime.setDate(rs.getDate("Date"));
				overtime.setTotalHours(rs.getDouble("TotalHours"));
				overtime.setStatus(rs.getString("Status"));
				overtime.setCreateBy(rs.getInt("CreateBy"));
				overtime.setCreateDate(rs.getDate("CreateDate"));
				overtime.setModifiedBy(rs.getInt("ModifiedBy"));
				overtime.setModifiedDate(rs.getDate("ModifiedDate"));
				overtime.setDescription(rs.getString("Description"));	
				items.add(overtime);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	
	}
